# @Desc    : do what 
# @Author  : meihongliang  
# @Time    : 2025-04-01 20:15
import pymysql
from pymysql import cursors

# 数据库配置（示例配置，请根据实际情况修改）
SOURCE_DB = {
    'host': 'localhost',
    'port': 3306,  # 1号数据库端口
    'user': 'username',
    'password': 'password',
    'db': 'database_name',
    'charset': 'utf8mb4'
}

# 其他12个数据库的端口列表
TARGET_PORTS = [3307, 3308, 3309, 3310, 3311, 3312,
                3313, 3314, 3315, 3316, 3317, 3318]

# 要操作的表名和查询条件
TABLE_NAME = 'your_table_name'
QUERY_CONDITION = 'WHERE status = 1 LIMIT 30'  # 从源表查询数据的条件
DELETE_CONDITION = 'WHERE date < "2023-01-01"'  # 删除目标表数据的条件


def get_source_data():
    """从源数据库获取需要同步的数据"""
    try:
        conn = pymysql.connect(**SOURCE_DB)
        with conn.cursor(cursor=cursors.DictCursor) as cursor:
            # 执行查询
            sql = f"SELECT * FROM {TABLE_NAME} {QUERY_CONDITION}"
            cursor.execute(sql)
            data = cursor.fetchall()
            print(f"从源数据库获取到{len(data)}条数据")

            # 打印每条记录的字段内容
            print("\n===== 数据内容详情 =====")
            for i, record in enumerate(data, 1):
                print(f"\n记录 #{i}:")
                for field_name, value in record.items():
                    print(f"  {field_name}: {value}")
                print("-" * 30)  # 分隔线

            for row in data:
                print(f"ID: {row['id']}, Name: {row['name']}, Age: {row['age']}")

            return data
    except Exception as e:
        print(f"获取源数据失败: {e}")
        return None
    finally:
        if 'conn' in locals() and conn.open:
            conn.close()


def sync_to_target(data):
    """将数据同步到目标数据库（先删除符合条件的数据，再插入新数据）"""
    if not data:
        print("没有可同步的数据")
        return

    # 获取列名用于构建INSERT语句
    columns = list(data[0].keys())
    columns_str = ', '.join(columns)
    placeholders = ', '.join(['%s'] * len(columns))

    # 为每个目标数据库创建连接并同步数据
    for port in TARGET_PORTS:
        target_config = SOURCE_DB.copy()
        target_config['port'] = port
        conn = None
        try:
            conn = pymysql.connect(**target_config)
            with conn.cursor() as cursor:
                # 1. 先删除符合条件的数据
                delete_sql = f"DELETE FROM {TABLE_NAME} {DELETE_CONDITION}"
                cursor.execute(delete_sql)
                deleted_rows = cursor.rowcount
                print(f"从端口 {port} 的数据库中删除了 {deleted_rows} 条数据")

                # 2. 插入新数据（使用INSERT IGNORE避免重复）
                insert_sql = f"INSERT IGNORE INTO {TABLE_NAME} ({columns_str}) VALUES ({placeholders})"
                cursor.executemany(insert_sql, [tuple(item.values()) for item in data])
                conn.commit()
                print(f"成功同步 {cursor.rowcount} 条数据到端口 {port} 的数据库")
        except Exception as e:
            print(f"同步到端口 {port} 的数据库失败: {e}")
            if conn:
                conn.rollback()
        finally:
            if conn and conn.open:
                conn.close()


if __name__ == '__main__':
    # 1. 从源数据库获取数据
    source_data = get_source_data()

    # 2. 字段修改逻辑（新增部分）----
    if source_data:
        for item in source_data:
            # 示例1: 添加前缀
            item['name'] = "[前缀]" + item['name']

            # 示例2: 添加后缀（根据需要任选一种）
            # item['name'] = item['name'] + "_后缀"

            # 示例3: 关键字替换
            # item['name'] = item['name'].replace("旧词", "新词")
    # ----------------------------

    # 3. 将数据同步到目标数据库
    if source_data:
        sync_to_target(source_data)
